package com.panopset.db;

import java.io.StringWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.dbcp.BasicDataSource;

/**
 * Database manager. Superclass should be a singleton.
 * 
 * @author Karl Dinwiddie
 * 
 */
public abstract class DBmgr {

	/**
	 * com.mysql.jbc.Driver.
	 */
	public static final String MYSQL_DRIVER = "com.mysql.jdbc.Driver";

	/**
	 * @return New connection definition.
	 */
	protected abstract ConnectionDefinition createConnectionDefinition();

	/**
	 * Call Class.forName(getDs().getDriverClassName()).
	 */
	protected abstract void createDriver();

	/**
	 * Database connection information.
	 */
	private ConnectionDefinition cd;

	/**
	 * @return Connection definition.
	 */
	public final ConnectionDefinition getCd() {
		if (cd == null) {
			cd = createConnectionDefinition();
		}
		return cd;
	}

	/**
	 * @return Connection.
	 */
	private Connection getConn() {
		try {
			return getDs().getConnection();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * Data source.
	 */
	private BasicDataSource ds;

	/**
	 * Initialize database driver if not yet initialized, and return a data
	 * source.
	 * 
	 * @return Commons connection pool data source.
	 */
	protected final BasicDataSource getDs() {
		if (ds == null) {
			ds = new BasicDataSource();
			ds.setDriverClassName(getCd().getDriver());
			ds.setUrl(getCd().getUrl());
			ds.setUsername(getCd().getUserName());
			ds.setPassword(getCd().getPassword());
			try {
				Class.forName(getCd().getDriver());
			} catch (ClassNotFoundException e) {
				System.out.println("Where is your MySQL JDBC Driver?");
				e.printStackTrace();
			}
		}
		return ds;
	}

	/**
	 * Static list of existing tables.
	 */
	private final List<String> tables = new ArrayList<String>();

	public List<String> getTables() {
		return tables;
	}

	/**
	 * Close the data source, call this on application shutdown.
	 */
	public final void close() {
		try {
			getDs().close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public void printDataSourceStats() {
		System.out.println("NumActive: " + getDs().getNumActive());
		System.out.println("NumIdle: " + getDs().getNumIdle());
	}

	public long execUpdate(final SqlSpec dc, final String idFieldName) {
		String msql = dc.getSql().replace("\n", "");
		for (Entry<Integer, Object> es : dc.getParams().entrySet()) {
			StringWriter sw = new StringWriter();
			if (es.getValue() instanceof Integer) {
				sw.append(es.getValue().toString());
			} else {
				sw.append("'");
				sw.append(es.getValue().toString().replace("$", "\\$"));
				sw.append("'");
			}
			int i = msql.indexOf("?");
			if (i == -1) {
				throw new RuntimeException(dc.toString() + " missing ?.");
			}
			msql = msql.replaceFirst("\\?", sw.toString());
		}
		try (Connection conn = getConn();
				PreparedStatement stmt = conn.prepareStatement(msql,
						new String[] { idFieldName })) {
			stmt.executeUpdate(msql, Statement.RETURN_GENERATED_KEYS);
			long rtn = -1;
			ResultSet rs = stmt.getGeneratedKeys();
			if (rs != null && rs.next()) {
				rtn = rs.getLong(1);
			}
			stmt.close();
			return rtn;
		} catch (Exception e) {
			throw new RuntimeException(dc.toString(), e);
		}
	}

	/**
	 * @param sql
	 *            SQL to execute.
	 */
	public final void execNoParamSql(final String sql) {
		exec(new SqlSpec(sql));
	}

	public void exec(SqlSpec sqlSpec) {
		resolveParams(sqlSpec);
		try (Connection conn = getConn();
				PreparedStatement stmt = conn
						.prepareStatement(sqlSpec.getSql())) {
			addParamsToStatement(stmt, sqlSpec);
			stmt.execute();
			stmt.close();
		} catch (Exception e) {
			throw new RuntimeException(sqlSpec.toString(), e);
		}
	}

	/**
	 * @param sql
	 *            SQL to execute.
	 * @return Results.
	 */
	public final List<Map<String, Object>> qry(final String sql) {
		return qry(new SqlSpec(sql));
	}

	/**
	 * @param sqlSpec
	 *            SQL statement and parameters to execute.
	 * @return Results.
	 */
	public final List<Map<String, Object>> qry(final SqlSpec sqlSpec) {
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();

		if (!resolveParams(sqlSpec)) {
			return result;
		}
		try (Connection conn = getConn();
				PreparedStatement stmt = conn
						.prepareStatement(sqlSpec.getSql())) {
			addParamsToStatement(stmt, sqlSpec);
			stmt.execute();
			ResultSet rs = stmt.getResultSet();
			List<String> columns = new ArrayList<String>();
			if (rs == null) {
				return result;
			}
			ResultSetMetaData rsMetaData = rs.getMetaData();
			if (rsMetaData == null) {
				return result;
			}
			int columnCount = rsMetaData.getColumnCount();
			if (columnCount < 1) {
				return result;
			}
			for (int i = 0; i < columnCount; i++) {
				columns.add(rsMetaData.getColumnLabel(i + 1));
			}
			while (rs.next()) {
				Map<String, Object> row = new HashMap<String, Object>();
				for (String key : columns) {
					row.put(key, rs.getObject(key));
				}
				result.add(row);
			}
			rs.close();
			stmt.close();
		} catch (Exception e) {
			throw new RuntimeException(sqlSpec.toString(), e);
		}
		return result;
	}

	private void addParamsToStatement(PreparedStatement stmt, SqlSpec dc)
			throws SQLException {
		for (Entry<Integer, Object> es : dc.getParams().entrySet()) {
			Object v = es.getValue();
			if (v instanceof Long) {
				stmt.setLong(es.getKey(), (Long) v);
			} else if (v instanceof Integer) {
				stmt.setLong(es.getKey(), (Integer) v);
			} else if (v instanceof Timestamp) {
				stmt.setTimestamp(es.getKey(), (Timestamp) v);
			} else if (v instanceof String) {
				stmt.setString(es.getKey(), (String) v);
			} else {
				throw new RuntimeException(v.getClass().getCanonicalName());
			}
		}
	}

	private boolean resolveParams(SqlSpec dc) {
		for (Entry<Integer, Object> es : dc.getParams().entrySet()) {
			if (es.getValue() instanceof ArrayList) {
				for (Object lp : ((ArrayList<?>) es.getValue())) {
					if (!addStmtParam(dc, es.getKey(), lp)) {
						return false;
					}
				}
			} else {
				if (!addStmtParam(dc, es.getKey(), es.getValue())) {
					return false;
				}
			}
		}
		return true;
	}

	private boolean addStmtParam(SqlSpec dc, int i, Object value) {
		if (value instanceof DataObject) {
			DataObject row = (DataObject) value;
			if (row.getId() == DataObject.NOID) {
				long id = row.getTableDescription().getIdByIndexes(row);
				if (id == DataObject.NOID) {
					return false;
				}
				row.setId(id);
			}
			dc.setObject(i, row.getId());
		} else {
			dc.setObject(i, value);
		}
		return true;
	}
}
